import pandas as pd
from sklearn.preprocessing import MinMaxScaler
import pymysql
import time
from datetime import datetime


import pymysql.cursors


class MysqlUtils(object):
    def __init__(self):
        self.conn= pymysql.connect(
            host='localhost',
            user="root",
            passwd="MYSQL123",
            db="scenic",
            port=3306,
            charset="utf8"
        )
            
    def get_scenic_data(self):
        """"
        获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = ''' 
        SELECT
        order_id,
        age,
            user_count,
            id_no,
            user_name,
            phone
        FROM (
            SELECT o.id as order_id, u.id_no, u.user_name,u.phone,
            CASE
                WHEN LENGTH(u.id_no) = 18 THEN YEAR(NOW()) - CAST(SUBSTR(u.id_no,7,4) as SIGNED)
                    ELSE NULL
                END AS age,
            (SELECT count(*) FROM ticket_order_user_rel WHERE order_id = o.id) as user_count            
            FROM ticket_order o join ticket_order_user_rel u on u.order_id = o.id WHERE u.id_no is not null and u.id_no != '') as subquery
            WHERE user_count = 1 and (age < 18 or age >= 60)
        
        '''
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df.head)
        # #格式转换
        # date_range = pd.date_range(start='2024-07-01',end='2025-03-02',freq='D')
        df.to_csv('./6/sennic_data.csv')
        
if  __name__=='__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()